/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package talvan;

import java.util.ArrayList;
import java.util.Date;

/**
 * Klassi sum sendur SQL Quarries fyri NewFundurPanel
 * @author DJH
 */
public class SQLCallerNewMeeting {
    
    private DbHandler dbh;
    private int userID;
    
    public SQLCallerNewMeeting(DbHandler dbh, int userID)
    {
        this.dbh = dbh;
        this.userID = userID;
    }
    
    
    /**
     * Metodir til NewTiltak og NewFundur panle
     * sendur SQL quarry sum spyr eftir Employes
     */
    public ArrayList<Employe> getEmployes()
    {
        ArrayList<Employe> resultList = new ArrayList<Employe>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Employe.firstName as name, Employe.id as id, Devision.name as devName, Devision.id as devision "
                     + "FROM Employe JOIN Devision "
                     + "ON Devision.id = Employe.devision_id ";
                
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                while (rs.next()) {
                    Employe newEmploye = new Employe();
                    
                    newEmploye.setFirstName(rs.getString("name"));
                    String idString = rs.getString("id");
                    newEmploye.setID(Integer.parseInt(idString));
                    newEmploye.setDevisionID(Integer.parseInt(rs.getString("devision")));
                    newEmploye.setDevisionName(rs.getString("devName"));
                    resultList.add(newEmploye);
                    
                }
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        return resultList;
    }
    
    /**
     * metoda sum hentar employes frá einari devision
     * @param intdevision
     * @return 
     */
    public ArrayList<Employe> getEmployes(int devisionID)
    {
        ArrayList<Employe> resultList = new ArrayList<Employe>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Employe.firstName as name, Employe.id as id, Devision.name as devName, devision.id as devID "
                     + "FROM Employe JOIN Devision "
                     + "ON Devision.id = Employe.devision_id "
                     + "WHERE Devision.id = " + devisionID + " ; ";
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                int counter = 0;
                //loop sum creatar objects og addar í array ella Arraylist?
                while (rs.next()) {
                    counter++;
                    System.out.println("COUNTER = " + counter);
                    Employe newEmploye = new Employe();
                    
                    newEmploye.setFirstName(rs.getString("name"));
                    String idString = rs.getString("id");
                    newEmploye.setID(Integer.parseInt(idString));
                    newEmploye.setDevisionID(Integer.parseInt(rs.getString("devID")));
                    newEmploye.setDevisionName(rs.getString("devName"));
                    resultList.add(newEmploye);
                }
                
                System.out.println("NUMBE OF " + resultList.size());
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        return resultList;
    }
    
    /**
     * Metoda sum heintar allar devisions frá dátagrunninum
     * @return list við devisions
     */
    public ArrayList<Devision> getDevisions()
    {
        ArrayList<Devision> resultList = new ArrayList<Devision>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Devision.name as name, Devision.id as id "
                     + "FROM Devision ";
                
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //loop sum creatar objects og addar í array ella Arraylist?
                while (rs.next()) {
                    Devision d = new Devision();
                    d.setName(rs.getString("name"));
                    String id = rs.getString("id");
                    d.setId(Integer.parseInt(id));
                    System.out.println("devision = " + d.getId());
                    resultList.add(d);
                }
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        return resultList;
    }

    /**
     * Metoda sum tekur ein string og convertar hann til eitt Date type
     * @param stringDate
     * @return Date
     */
    private Date convertToDate(String stringDate)
    {
        int year = Integer.parseInt(stringDate.substring(0, 4));
        int month = Integer.parseInt(stringDate.substring(5, 7));
        int day = Integer.parseInt(stringDate.substring(8, 10));
        int hour = Integer.parseInt(stringDate.substring(11, 13));
        int min = Integer.parseInt(stringDate.substring(14, 16));
                    
        Date date = new Date(year, month, day, hour, min);
        return date;
    }
    
    
    /**
     * Metoda Creatar eitt Meeting, og returnar id nummari hjá tí
     * @param meetingString
     * @return 
     */
    public int createMeeting(String name, String details, String stringDate)
    {
        int meetingID = -1;
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "INSERT INTO Meeting "
                     + "OUTPUT INSERTED.id "
                     + "VALUES ('" + name + "','" + details + "'," + userID + ", '" + stringDate + "');";
                             //('Fundur 5',' u mad bro', 2, '12-06-2012 17:30:000.00')
                //US//INSERTE TALVAN;
                //INSERT INTO Meeting 
                //OUTPUT INSERTED.id VALUES ('Fundur 5',' u mad bro', 2, '12-06-2012 17:30:000.00');
 
                
                System.out.println("SQL STRING = " + sql);
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //skal returna ein int
                //rs.next();
                System.out.println("Bø ="+rs.getString(1));
                meetingID = rs.getInt("id");
                System.out.println("TEST " + meetingID);
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        //if(meetingID == -1)
            //throw new Exception("error í SQLCaller meeting returnai -1");
        
       return meetingID;
    }
    
    
    /**
     * SQL quarry sum skal generata
     */
    public void createInvitations(ArrayList<Employe> inviteList, int meetingID)
    {
        String message = "";
        
        for(Employe e : inviteList)
        {
            message += "INSERT INTO Invitation VALUES (" + meetingID + "," + e.getDevisionID() + "); \n";
                    //INSERT INTO Invitation VALUES (1,1);
        }
        
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += message;
                
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //skal returna ein int
                rs.next();
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
        
    }
    
    
    
}
